package DAL;

import Entity.Books;
import IDAL.BookPort;
import Utils.JDBCUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.*;


import java.sql.Connection;
import java.util.Date;
import java.util.List;


/**
 * @Author Chen
 * @Date 2022/1/7 10:39
 * @Version 1.0
 */
public class BooksDao implements BookPort {


    //添加图书信息
    @Override
    public int addBook(Object... args) {
        int result = 0;
        Connection conn = null;
        try {
            QueryRunner runner = new QueryRunner();
            conn = JDBCUtils.getConnection3();
            String sql = "insert into l_books(bookNumber,bookName,author,press,publicationDate,printingTimes,unitPrice, bookTypeNumber)values(?,?,?,?,?,?,?,?)";

            result = runner.update(conn, sql, args);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(conn, null);
        }
        return result;
    }

    //删除图书信息
    @Override
    public int deleteBook(Object... args) {
        int result = 0;
        Connection conn = null;
        try {
            QueryRunner runner = new QueryRunner();
            conn = JDBCUtils.getConnection3();
            String sql = "delete from l_books where bookNumber=?";
            result = runner.update(conn, sql, args);

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(conn, null);
        }
        return result;
    }

    //修改图书信息
    @Override
    public int updateBook(Object... args) {
        int result = 0;
        Connection conn = null;
        try {
            QueryRunner runner = new QueryRunner();
            conn = JDBCUtils.getConnection3();
            String sql = "update l_books set " +
                    " bookName = ?," +
                    " author = ?," +
                    " press = ?," +
                    " publicationDate = ?," +
                    "`printingTimes` = ?," +
                    " unitPrice = ?," +
                    " bookTypeNumber = ? " +
                    " where bookNumber = ?";
            result = runner.update(conn, sql, args);

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(conn, null);
        }
        return result;
    }

    //修改图书未借出本数
    @Override
    public int updateBookCountById(Object... args) {
        int result = 0;
        Connection conn = null;
        try {
            QueryRunner runner = new QueryRunner();
            conn = JDBCUtils.getConnection3();
            String sql = "update l_books set " +
                    "`printingTimes` = ? " +
                    "where bookNumber = ?";
            result = runner.update(conn, sql, args);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(conn, null);
        }
        return result;
    }


    //查询所有图书
    @Override
    public List<Books> selectAllBooks() {
        List<Books> result = null;
        Connection conn = null;
        try {
            QueryRunner runner = new QueryRunner();
            conn = JDBCUtils.getConnection3();
            String sql = "select * from l_books";
            BeanListHandler<Books> handler = new BeanListHandler<>(Books.class);
            result = runner.query(conn, sql, handler);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(conn, null);
        }
        return result;
    }

    //根据id查询图书
    @Override
    public Books selectBookById(Object... args) {
        Books result = null;
        Connection conn = null;
        try {
            QueryRunner runner = new QueryRunner();
            conn = JDBCUtils.getConnection3();
            String sql = "select * from l_books where bookNumber = ?";
            BeanHandler<Books> handler = new BeanHandler<>(Books.class);
            result = runner.query(conn, sql, handler, args);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(conn, null);
        }
        return result;
    }

    //根据书名查询内容
    @Override
    public List<Books> selectBookByName(Object... args) {
        List<Books> result = null;
        Connection conn = null;
        try {
            QueryRunner runner = new QueryRunner();
            conn = JDBCUtils.getConnection3();
            String sql = "select * from l_books where bookName = ?";
            BeanListHandler<Books> handler = new BeanListHandler<>(Books.class);
            result =  runner.query(conn, sql, handler, args);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(conn, null);
        }
        return result;
    }

    //根据图书类型查询
    @Override
    public List<Books> selectBookByType(Object... args) {
        List<Books> result = null;
        Connection conn = null;
        try {
            QueryRunner runner = new QueryRunner();
            conn = JDBCUtils.getConnection3();
            String sql = "select * from l_books where bookTypeNumber = ? ";
            BeanListHandler<Books> handler = new BeanListHandler<>(Books.class);
            result = runner.query(conn, sql, handler, args);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(conn, null);
        }
        return result;
    }

}
